﻿using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using SmallDataBase.DTO;
using Zack.Tools.Extension;


namespace SmallDataBase.MySQL
{
    public class SQLBuilder : ISQLBuilder
    {
        #region ISQLBuilder Members

        public string BuildInsertSQL(MetaDTO dto)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            IEnumerable<FieldDTO> columnFields = dto.GetFields(true, true, false);
            string columns = GetColumnsSQL(columnFields);
            string parameter = GetParameterSQL(columnFields);
            sql.AppendFormat("insert into {0} ({1}) values ({2});", table, columns, parameter);
            return sql.ToString();
        }
        public string BuildInsertSQLAndReturnLastInsertID(MetaDTO dto)
        {
            return BuildInsertSQL(dto) + "SELECT LAST_INSERT_ID();";
        }

        public string BuildUpdateSQL(MetaDTO dto)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            IEnumerable<FieldDTO> columnFields = dto.GetFields(true, false, false);
            string columns = GetUpdateFieldsSQL(columnFields);
            IEnumerable<FieldDTO> condition = dto.GetFields(false, true, true);

            if (condition.Any())
            {
                sql.AppendFormat("update {0} set {1} where {2};", table, columns, GetWhereSQL(condition));
            }
            else
            {
                //无主键对象
                sql.AppendFormat("update {0} set {1};", table, columns);
            }
            return sql.ToString();
        }

        public string BuildDeleteSQL(MetaDTO dto, MetaDTO queryObj)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            IEnumerable<FieldDTO> where = queryObj.GetFields(true, true, true);
            sql.AppendFormat("delete from {0} where {1}", table, GetWhereSQL(where));
            return sql.ToString();
        }

        public string BuildDeleteSQL(MetaDTO dto, params SQLQueryGroupDTO[] queryObj)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            sql.AppendFormat("delete from {0} where {1}", table, GetWhereSQL(queryObj));
            return sql.ToString();
        }

        public string BuildSeletSQL(MetaDTO dto, MetaDTO queryObj)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            IEnumerable<FieldDTO> columnFields = dto.GetFields(true, true, true);
            string columns = GetColumnsSQL(columnFields);

            string format = "select {0} from {1} where {2}";
            IEnumerable<FieldDTO> where = queryObj.GetFields(true, true, true);
            sql.AppendFormat(format, columns, table, GetWhereSQL(where));
            return sql.ToString();
        }

        public string BuildSeletSQL(MetaDTO dto, params SQLQueryGroupDTO[] queryObj)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            IEnumerable<FieldDTO> columnFields = dto.GetFields(true, true, true);
            string columns = GetColumnsSQL(columnFields);

            string format = "select {0} from {1} where {2}";
            sql.AppendFormat(format, columns, table, GetWhereSQL(queryObj));
            return sql.ToString();
        }

        public string BuildSeletSQL(MetaDTO dto)
        {
            var sql = new StringBuilder();
            string table = GetTableName(dto);
            IEnumerable<FieldDTO> columnFields = dto.GetFields(true, true, true);
            string columns = GetColumnsSQL(columnFields);
            string format = "select {0} from {1}";
            sql.AppendFormat(format, columns, table);
            return sql.ToString();
        }

        public IEnumerable<DbParameter> BuildParameter(MetaDTO dto)
        {
            return
                dto.GetFields(true, true, true).Select(item => new MySqlParameter("@" + item.Name, item.Value)).Cast
                    <DbParameter>();
        }

        public IEnumerable<DbParameter> BuildParameter(SQLQueryGroupDTO dto)
        {
            return
                dto.SQLQueryDTOs.Select(item => new MySqlParameter("@" + item.Name, item.Value)).Cast
                    <DbParameter>();
        }

        public DbCommand BuildCommand()
        {
            return new MySqlCommand();
        }

        public DbConnection BuildConnection(ConfigDTO config)
        {
            return new MySqlConnection(config.ConnectionString);
        }

        #endregion

        private static string GetTableName(MetaDTO dto)
        {
            return string.IsNullOrEmpty(dto.DataBaseName)
                       ? "`" + dto.TableName + "`"
                       : "`" + dto.DataBaseName + "`.`" + dto.TableName + "`";
        }

        #region 生成sql语句中的字段部分

        private static string GetColumnsSQL(IEnumerable<FieldDTO> columnFields)
        {
            return GetFieldsPartSQL("`{0}`,", columnFields, 1);
        }

        private static string GetParameterSQL(IEnumerable<FieldDTO> columnFields)
        {
            return GetFieldsPartSQL("@{0},", columnFields, 1);
        }

        private static string GetWhereSQL(IEnumerable<FieldDTO> columnFields)
        {
            return GetFieldsPartSQL(" `{0}`=@{0} and", columnFields, 4);
        }

        private static string GetWhereSQL(SQLQueryGroupDTO[] queryObj)
        {
            var sb = new StringBuilder();
            foreach (SQLQueryGroupDTO groupDTO in queryObj)
            {
                sb.Append(" (");
                foreach (SQLQueryDTO queryDTO in groupDTO.SQLQueryDTOs)
                {
                    string oper = string.Empty;
                    switch (queryDTO.Operator)
                    {
                        case SQLOperators.Like:
                            oper = "like";
                            break;
                        case SQLOperators.Unequal:
                            oper = "<>";
                            break;
                        case SQLOperators.Equal:
                            oper = "=";
                            break;
                        case SQLOperators.GreaterThan:
                            oper = ">";
                            break;
                        case SQLOperators.LessThan:
                            oper = "<";
                            break;
                        case SQLOperators.GreaterThanEqual:
                            oper = ">=";
                            break;
                        case SQLOperators.LessThanEqual:
                            oper = "<=";
                            break;
                    }
                    sb.AppendFormat(" `{0}` {1} @{0} {2}", queryDTO.Name, oper,
                                    groupDTO.GroupType == GroupType.And ? "And" : " Or");
                }
                sb.RemoveLastChar(3);
                sb.Append(") and");
            }
            sb.RemoveLastChar(3);
            return sb.ToString();
        }

        private static string GetUpdateFieldsSQL(IEnumerable<FieldDTO> columnFields)
        {
            return GetFieldsPartSQL("`{0}`=@{0},", columnFields, 1);
        }

        //返回字段组成的sql语句部分
        private static string GetFieldsPartSQL(string format, IEnumerable<FieldDTO> columnFields, int lastRemoveLength)
        {
            var parameter = new StringBuilder();
            foreach (FieldDTO item in columnFields)
            {
                parameter.AppendFormat(format, item.Name);
            }
            parameter.RemoveLastChar(lastRemoveLength);
            return parameter.ToString();
        }

        #endregion


       
    }
}